#-*- coding: UTF-8 -*-
__author__ = 'Administrator'
import pymysql,argparse,sys,xlwt,os,logging
fields = []
fieldsName = [u'库名',u'表名',u'列名',u'是否为空',u'数据类型',u'字段说明']
colWidthBasic = 200
colWidthMax = 8000
colWidthMin = 2000

def getFields(host,port,user,password,info):
    global fields
    info = str(info)
    conn = pymysql.connect(host=host,port=port,user=user,password=password,db='information_schema',use_unicode=True, charset="utf8")
    cursor = conn.cursor()
    if info.find('.') == -1:
        info = info+'.'
    sql = 'select table_schema,table_name,column_name,if(is_nullable="yes","是","否"),column_type,column_comment from columns where concat(table_schema,".",table_name) like "%{0}%"'.format(info)
    cursor.execute(sql)
    fields = list(cursor.fetchall())
    if(len(fields)==0):
        logging.info('no fields found matching the condition')
        sys.exit()
    pass

def generateXls(file):
    if os.path.splitext(file)[1]!='.xls':
        logging.error('invalid output file format')
        sys.exit()
    xls = xlwt.Workbook()
    sht1 = xls.add_sheet('main')
    div = 0
    table = None
    #设置表头
    for i in range(6):
        sht1.write(0,i,fieldsName[i])
    colWidth = [0]*6
    #设置内容
    for i in range(len(fields)):
        if fields[i][1] != table:
                table = fields[i][1]
                div = div+1
        for j in range(6):
            sht1.write(i+div,j,fields[i][j])
            if len(fields[i][j])>colWidth[j]:
                colWidth[j] = len(fields[i][j])
    #设置列宽
    for i in range(6):
        width = colWidthBasic*colWidth[i]
        if width<colWidthMin:
            width = colWidthMin
        if width>colWidthMax:
            width = colWidthMax
        sht1.col(i).width = width
    xls.save(file)
    pass

if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO)
    parser = argparse.ArgumentParser()
    parser.add_argument('dict',help='dict file')
    parser.add_argument('info',help='database and table to be generated,supported format like db.table,db')
    parser.add_argument('-host',default='localhost',help='db host')
    parser.add_argument('-port',default=3306,help='db port')
    parser.add_argument('-user',default='root',help='db user')
    parser.add_argument('-password',default='',help='db password')
    args = parser.parse_args()
    logging.info('starting to generate dict')
    getFields(args.host,args.port,args.user,args.password,args.info)
    generateXls(args.dict)
    logging.info('generating complete')
    pass